﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace 查快递的小工具
{
    public class ExcelHelper
    {

        /// <summary>
        /// Excel转DataTable
        /// </summary>
        /// <param name="filepath">Excel表路径</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filepath)
        {

            DataTable result = new DataTable();
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(filepath); //获取扩展名
            try
            {
                using (FileStream fs = File.OpenRead(filepath))
                {
                    if (extension.Equals(".xls")) //2003
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else                         //2007以上
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                }

                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                //构建DataTable
                IRow row = sheet.GetRow(0);
                result = BuildDataTable(row);
                if (result != null)
                {
                    if (sheet.LastRowNum >= 1)
                    {
                        for (int i = 1; i < sheet.LastRowNum + 1; i++)
                        {
                            IRow temp_row = sheet.GetRow(i);
                            if (temp_row == null) { continue; }
                            List<object> itemArray = new List<object>();
                            for (int j = 0; j < result.Columns.Count; j++)
                            {
                                itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : GetValueType(temp_row.GetCell(j)));
                            }

                            result.Rows.Add(itemArray.ToArray());
                        }
                    }
                }
                return result;

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }



        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;
            IWorkbook workbook = null;

            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook();
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook();

                try
                {
                    if (workbook != null)
                    {
                        sheet = workbook.CreateSheet(sheetName);
                    }
                    else
                    {
                        return -1;
                    }

                    if (isColumnWritten == true) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }

                    for (i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                        }
                        ++count;
                    }
                    workbook.Write(fs); //写入到excel

                    return count;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }




        /// <summary>
        /// 构建表头
        /// </summary>
        /// <param name="Row"></param>
        /// <returns></returns>
        private static DataTable BuildDataTable(IRow Row)
        {
            DataTable result = null;
            if (Row.Cells.Count > 0)
            {
                result = new DataTable();
                for (int i = 0; i < Row.LastCellNum; i++)
                {
                    result.Columns.Add(Row.GetCell(i).ToString());
                }
            }
            return result;
        }


        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    else//其他数字类型
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }
}
